# -*- coding: utf-8 -*-
# author:WS
# time:2022/1/12/15:30:10
# ___msg___ = '通过申请号查询专利法律状态，发明人，代理机构，代理人等专利详细数据'
import xlwt
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait
from quote_folder.Db_connect import DB_connect
from selenium.webdriver.common.by import By
from selenium import webdriver
from bs4 import BeautifulSoup
import time
import random
import re
from 查询并更新系统专利状态 import is_search_ok


class Patent_Data():
    def __init__(self):
        self.get_url = "http://cpquery.cnipa.gov.cn/"
        self.develop_con = DB_connect().online_db()  # 开发数据库连接
        self.develop_cur = self.develop_con.cursor()
        self.bigdata_con = DB_connect().online_big_data()  # 数据组数据库连接
        self.bigdata_cur = self.bigdata_con.cursor()
        self.redis_con = DB_connect().gongju()  # redis连接
        self.redis_db = DB_connect().patents_reids()
        self.info_list = []
        # self.applicant = applicant

    def login_gov(self, port):
        """ 登录中国及多国 """

        # """ 登录中国及多国(信合红谷) """
        profile = webdriver.FirefoxOptions()
        profile.set_preference('network.proxy.type', 1)
        browser = webdriver.Firefox(options=profile, executable_path=r'D:\工具\geckodriver-v0.15.0-win64\geckodriver.exe')
        browser.get(self.get_url)
        time.sleep(5)
        WebDriverWait(browser, 10).until(EC.presence_of_element_located((By.ID, "username"))).clear()
        if port == 'XH':
            browser.find_element_by_id('username').send_keys('CN00675769')
            time.sleep(1)
            browser.find_element_by_id('password').clear()
            browser.find_element_by_id('password').send_keys('Gv89NWWY@')

        if port == 'XHHG':
            browser.find_element_by_id('username').send_keys('42264')
            time.sleep(1)
            browser.find_element_by_id('password').clear()
            browser.find_element_by_id('password').send_keys('Xhhg0613@@')

        if port == 'WB':
            browser.find_element_by_id('username1').send_keys('17754433386')
            time.sleep(1)
            browser.find_element_by_id('password').clear()
            browser.find_element_by_id('password1').send_keys('Fj123456~')

        is_search_ok(browser)
        current_url = browser.current_url
        return browser, current_url

    # 2020207090033
    # 2019305893887

    username_info_list = [
        {"username": "15027399434", "password": "YJyj!1234"},
        {"username": "18071440149", "password": "Xhhg123456!"},
        {"username": "15978554102", "password": "Hhr31415926!"},
        {"username": "17362997328", "password": "Ccy1994!"},
        {"username": "15090891351", "password": "Ccy1994!"},
        {"username": "13283766953", "password": "Ws1234567890~"},
        {"username": "13296529358", "password": "king123!B"},
        {"username": "18672982996", "password": "Ws1296817327~"},
        {"username": "17634417772", "password": "Ws123456cyh~"},
        {"username": "15039703722", "password": "WSws123456~"},
        {"username": "17710225394", "password": "Wq123456~"},
        {"username": "15318998458", "password": "123456Ws~"},
        {"username": '15938630689', 'password': "Whr123456~"},
        {"username": '18733151070', 'password': "Jx123456~"},
        {"username": '13995565120', 'password': "QWEqwe123~"},
        {"username": '15609508281', 'password': "Zq418317382@"},
        {"username": '15039738897', 'password': "Bl123456~"},
        {"username": '18240721210', 'password': "Zx123456~"},
        {"username": '15571590720', 'password': "Xj152588*"},
        {"username": '17630617464', 'password': "Zys123456~"},
        {"username": '17754433386', 'password': "Fj123456~"},  # 20
    ]

    def execute_sql(self, insert_sql):
        """
         执行sql插入
        :param insert_sql:
        :return:
        """
        try:
            self.bigdata_cur.execute(insert_sql)
        except Exception as E:
            print(insert_sql)
            print("插入错误：", E)

    def save_excel(self, info_list):
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet("sheet1")
        sheet.write(0, 0, '专利名称')
        sheet.write(0, 1, '申请人')
        sheet.write(0, 2, '申请日')
        sheet.write(0, 3, '国知局状态')
        sheet.write(0, 4, '联系人')
        sheet.write(0, 5, '电话')
        sheet.write(0, 6, '发明人')
        sheet.write(0, 7, '代理机构')
        sheet.write(0, 8, '费用类型')
        sheet.write(0, 9, '费用金额')
        sheet.write(0, 10, '截止日')
        sheet.write(0, 11, '代理人')
        sheet.write(0, 12, "申请号")

        if len(info_list) > 0:
            for i in range(len(info_list)):
                sheet.write(i+1, 0, info_list[i]['patent_name'])
                sheet.write(i + 1, 1, info_list[i]['sqren'])
                sheet.write(i + 1, 2, info_list[i]['sqr'])
                sheet.write(i + 1, 3, info_list[i]['Legal_Status'])
                sheet.write(i + 1, 6, info_list[i]['inventor'])
                sheet.write(i + 1, 7, info_list[i]['agency'])
                sheet.write(i + 1, 8, info_list[i]['money_type'])
                sheet.write(i + 1, 9, info_list[i]['money'])
                sheet.write(i + 1, 10, info_list[i]['close_day'])
                sheet.write(i + 1, 11, info_list[i]['agent'])
                sheet.write(i + 1, 12, info_list[i]['sqh'])
            workbook.save("AAAAAAAAA.xls")

    def get_data(self, soup, sqh, port):
        """
        获取专利数据
        :param soup:
        :param sqh:
        :return:
        """
        item = {}
        table_li = soup.select('table[class="imfor_table_grid"]')
        try:
            for table_num in range(len(table_li)):
                if table_num == 1:
                    tr_li = table_li[table_num].select("tr")
                    for tr_num in range(len(tr_li)):
                        if tr_num == 0:
                            name_text = tr_li[tr_num].select('td')[1].text
                            item['patent_name'] = name_text.strip()

            shenqh = soup.select('span[name="record_zlx:shenqingh"]')
            item["sqh"] = shenqh[0]['title'].strip()  # 申请号
            law_status = soup.select('span[name="record_zlx:anjianywzt"]')
            item['Legal_Status'] = law_status[0]['title'].strip()  # 法律状态
            shenqr = soup.select('span[name="record_zlx:shenqingr"]')
            item["sqr"] = shenqr[0]['title'].strip()  # 申请日
            zhufenlh = soup.select('span[name="record_zlx:zhufenlh"]')
            item["ipc_num"] = zhufenlh[0]['title'].strip()  # 主分类号
            shenqren = soup.select('span[name="record_sqr:shenqingrxm"]')
            item["sqren"] = shenqren[0]['title'].strip()  # 申请人
            famingren = soup.select("span[name='record_fmr:famingrxm']")
            item['inventor'] = famingren[0]['title'].strip()  # 发明人
            dailijg = soup.select("span[name='record_zldl:dailijgmc']")
            item['agency'] = dailijg[0]['title'].strip()  # 代理机构
            agent = soup.select("span[name='record_zldl:diyidlrxm']")
            item['agent'] = agent[0]['title'].strip()  # 代理人

        except:
            item['sqh'] = sqh
            item['Legal_Status'] = ''
            item["sqr"] = ''
            item["ipc_num"] = ''
            item["sqren"] = ''
            item['inventor'] = ''
            item['agency'] = ''
            item['agent'] = ''
        item = self.get_fee_info(sqh, item)   # 处理数据准备插入Excel
        # item = self.get_phone(sqh, item)
        self.info_list.append(item)   # 同上
        self.save_excel(self.info_list)
        # if port == 'WB':
        #     self.insert_into(sqh, item)  # 处理数据准备入库
        # self.update_mysql(item)  # 更新数据库数据

    def get_phone(self, sqh, item):
        sql = f"SELECT unique_number from finals WHERE application_number = 'ZL' + '{sqh}'"
        self.develop_cur.execute(sql)

    def get_fee_info(self, sqh, item):
        sql = f"SELECT money_type, money, close_date FROM `annual_fee_information` WHERE application_number = '{sqh}' LIMIT 1"
        self.bigdata_cur.execute(sql)
        return_data = self.bigdata_cur.fetchall()
        try:
            item['money_type'] = return_data[0][0]
            item['money'] = return_data[0][1]
            item['close_day'] = return_data[0][2]
        except:
            item['money_type'] = ''
            item['money'] = ''
            item['close_day'] = ''
        return item

    def get_html(self, sqh, browser, current_url, port):
        """
        获取专利信息html,抓取案件状态
        :param sqh:
        :param browser:
        :param current_url:
        :return:
        """
        # sqh = re.sub(r'ZL', '', sqh)
        pat = ''.join(re.findall(r'select-key:shenqingh=(.*?)&select-key:backPage', current_url))
        url = re.sub(pat, sqh, current_url)
        browser.get(url)
        count_page = browser.page_source
        soup = BeautifulSoup(count_page, "lxml")
        self.get_data(soup, sqh, port)

    def update_mysql(self, item):
        """
        将法律状态插入数据库更新字段
        """
        # shen = 'CN' + sqh.strip()[:-1] + '.' + sqh.strip()[-1]
        # update_sql = f"""UPDATE patent_table set name="{item['patent_name']}", application_number="{item['sqh']}", apply_day="{item['sqr']}", applicant="{item['sqren']}", inventor="{item['inventor']}", agency="{item['agency']}", agent="{item['agent']}", case_status="{item['Legal_Status']}", key_word="{item['sqren']}" WHERE application_number = '{sqh}'"""
        update_sql = f"""UPDATE patent_status_info set agency="{item['agency']}" WHERE application_number like '%{item['sqh']}%'"""
        try:
            print(update_sql)
            self.execute_sql(update_sql)
            self.bigdata_con.commit()
            print("插入数据成功！")
        except:
            print(update_sql)

    def get_db_sqh(self):
        sqhli = []
        sql = "SELECT application_number FROM patent_status_info WHERE agency IS NULL"
        self.bigdata_cur.execute(sql)
        returndata = self.bigdata_cur.fetchall()
        for it in returndata:
            sqhli.append(it[0].strip())
        return sqhli

    def run(self, port):
        """入口函数"""
        browser, current_url = self.login_gov(port)

        # sqh_li, len_num = self.select_sqh()

        # sqhli = self.get_db_sqh()
        # for application_number in sqhli:
        #     print(application_number)
        #     self.get_html(application_number, browser, current_url, port)  # 获取专利申请页面

        # count = 0
        # for item in sqh_li:
        #     count += 1
        #     print(f"共{len_num}条，当前正在抓取第{count}条")
        #     application_number = item[0]
        #     self.get_html(application_number, browser, current_url)  # 获取专利申请页面

        with open("sqh.txt", "r") as fw:
            ss = 0
            for application_number in fw:
                sqh = application_number.strip()
                if ss >= 999:
                    break
                # self.get_aaa(sqh)

                self.get_html(application_number, browser, current_url, port)  # 获取专利申请页面

        # need_time = 0
        # while self.redis_con.scard("外部订单申请号"):
        #     start_time = time.time()
        #     application_number = self.redis_con.spop("外部订单申请号")
        #     print('-' * 20,
        #           f"开始抓取 {application_number} 的信息, 剩余 {self.redis_con.scard('外部订单申请号')} 个, 大约 {need_time} 分钟",
        #           '-' * 20)
        #     print(f"开始抓取 {application_number}")
        #     self.get_html(application_number, browser, current_url)  # 获取专利申请页面
        #     end_time = time.time()
        #     need_time = round((end_time - start_time) * DB_connect().patents_reids().scard("外部订单申请号") / 60, 1)

        # # self.get_powerday_html(application_number, browser, )  # 授权日

        # self.bigdata_con.commit()
        # self.bigdata_cur.close()
        # self.bigdata_con.close()
        # self.develop_cur.close()
        # self.develop_con.close()
        # browser.close()

    def insert_into(self, sqh, item):
        sql = f"INSERT INTO patent_status_info (application_number, patent_name, apply_day, country_state, agency, agent) VALUES ('{sqh}', '', '{item['sqr']}', '{item['Legal_Status']}', '{item['agency']}', '{item['agent']}')"
        # sql = "INSERT INTO patent_table (`name`, application_number, apply_day, applicant, inventor, agency, agent, case_status, key_word)VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')".format(
        #     item['patent_name'], item['sqh'], item['sqr'], item['sqren'], item['inventor'], item['agency'],
        #     item['agent'], item['Legal_Status'], item['sqren'])
        try:
            self.execute_sql(sql)
            self.bigdata_con.commit()
            print("成功入库！")
        except Exception as e:
            print(sql, e)

    def select_sqh(self):
        sql = "SELECT application_number FROM patent_table WHERE `name` = ''"
        self.bigdata_cur.execute(sql)
        sqh_li = self.bigdata_cur.fetchall()
        len_num = len(sqh_li)
        return sqh_li, len_num

    def get_aaa(self, sqh):
        sqh = "ZL" + sqh
        sql = f'SELECT case_status, fmr, agency, first_agent FROM `big_data`.`xhhg_pantent_status` WHERE `application_number` = "{sqh}"'
        self.bigdata_cur.execute(sql)
        data = self.bigdata_cur.fetchall()


if __name__ == '__main__':
    # port = "XH"
    # port = "XHHG"
    port = "WB"
    func = Patent_Data()
    func.run(port)  # 2020207090033    公众查询用例
    # 2019305893887                xhhg账号查询用例
